<?php

namespace App\Http\Controllers\Admin\ExportReportSample;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use Illuminate\Support\Facades\Storage;
use \App\Http\Controllers\Admin\ItemDataController;

class HanZhongHanYuanController extends Controller
{

    /* 导出Excel 检测报告*/
    public function export(Request $request){
        $itemDataCon = new ItemDataController();
        $WeaStat = $itemDataCon->WeaStat();
        $contract_id = $request->id; //批次 id
        $data_batch = DB::table('a_contract')->find($contract_id);
        $batchNum = $data_batch->batchNum;
        $storeFile = 'reports/'.$batchNum.'.xls';
        $disk = Storage::disk('public');
        $new = $request->new;
        if(!$new && $disk->exists($storeFile) && $data_batch->status > 13){
            //return $disk->download($storeFile);
        }

        $setting1 = DB::table('setting')->find(1);
        $report_num_pre = $setting1->report_num_pre; //报告编号前缀
        $company_name = $setting1->name_rep; //获取检测公司名称
        $headerContent = "&R{$company_name}     {$report_num_pre}{$data_batch->batchNum}"; //眉头内容
        //$footerContent = '&R&12第 &P 页，共 &N 页'; //页脚内容
        $footerContent = '';

        //只生成样品的报告，每类样品一页报告
        $sql = "SELECT DISTINCT
        A.std_id,
        A.tool_name,
        A.tool_id,
        A.unit
        FROM
        a_contract_gaiyao AS A
        WHERE
        A.contract_id = :contract_id";

        //查询出当前批次下 标准、工器具的集合，并去重
        $data_std_tool = DB::select($sql, ['contract_id' => $contract_id]);
        $spreadsheet2 = $spreadsheet_common = $worksheet =null;
        foreach ($data_std_tool as $k => $v) {

            # 查询数据的位置信息，即数据要存到Excel的哪个单元格
            $info_pos = DB::table('a_excel_pos as B')
                ->leftJoin('a_std_tool_rel as A', 'A.id', '=', 'B.std_tool_rel_id')
                ->leftJoin('a_tool as C', 'C.id', '=', 'A.tool_id')
                ->where(['A.std_id' => $v->std_id, 'A.tool_id' => $v->tool_id, 'B.is_default' => 1])
                ->select('B.*', 'A.cycle', 'A.ctime', 'A.remark', 'C.name as tool_name')->first();
            if (!$info_pos) {
                return "{$v->tool_name} 未指定数据在模板文件中的位置";
            }
            $posname = $info_pos->name === '默认' ? '' : $info_pos->name;

            //未指定行号
            if ($info_pos->line_num === '' || intval($info_pos->line_num) < 5) {
                return "{$v->tool_name} 未指定数据在模板文件中的行号";
            }

            //==========================去具体工器具模版文件 复制表单 到 封面模板文件========================
            $template_file = "./storage/ExcelTemplate/{$v->std_id}/{$v->tool_name}{$posname}.xls";
            if (!file_exists($template_file)) {
                return "{$v->tool_name}的模板文件不存在";
            }
            if(!$spreadsheet2){
                $spreadsheet2 = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取被复制的模板文件
                $worksheet = $spreadsheet2->getActiveSheet(); //在MS Excel（或其他适当的Spreadsheet程序）中打开工作簿时将处于活动状态的工作表
            }else{
                $spreadsheet_common = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取被复制的模板文件
                $worksheet_common = $spreadsheet_common->getActiveSheet(); //在MS Excel（或其他适当的Spreadsheet程序）中打开工作簿时将处于活动状态的工作表
                $worksheet_common->setTitle("HelloWorld-gww{$k}");
                $spreadsheet2->addExternalSheet($worksheet_common); //添加外部工作表
                //为了防止内存泄漏，建议用完手动清理
                $spreadsheet_common->disconnectWorksheets();
                unset($spreadsheet_common);
                $worksheet = $spreadsheet2->getSheetByName("HelloWorld-gww{$k}"); //按表单名 获取到激活的表单
            }

            $worksheet->getHeaderFooter()->setOddHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(检测报告正文页眉头)  Odd
            $worksheet->getHeaderFooter()->setEvenHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(检测报告正文页眉头) Even
            $worksheet->getHeaderFooter()->setOddFooter($footerContent); //页脚  Odd
            $worksheet->getHeaderFooter()->setEvenFooter($footerContent); //页脚  Even


            //设置标题
            $worksheet->setTitle($v->tool_name);
            //getSheetCount()方法将告诉您工作簿中的工作表数量,class Spreadsheet 的方法
            $this->setPage($worksheet); //插入公章图片
            $DY_RiQi = strtotime($data_batch->created_at); //将表示时间和日期的字符串转化为相应的时间戳
            if ($info_pos->come_date !== '') {
                $comeDateCell = $info_pos->come_date;
                $worksheet->getCell($comeDateCell)->setValue(date("Y年m月d日", $DY_RiQi)); //到样日期
            }

            //------------------------------------------检测日期 start---------
            //检验日期开始时间
            $testdate_start = DB::table('a_sample_info as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->min('test_time');
            if ($testdate_start) {
                $testdate_start = strtotime($testdate_start); //将任何字符串的日期时间描述解析为 Unix 时间戳
                $testdate_start = date("Y年m月d日", $testdate_start);
            }
            //检验日期结束时间
            $testdate_end = DB::table('a_sample_info as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->max('test_time');
            if ($testdate_end) {
                $testdate_end = strtotime($testdate_end); //将任何字符串的日期时间描述解析为 Unix 时间戳
                $testdate_end = date("Y年m月d日", $testdate_end);
            }
            if ($testdate_start === $testdate_end) {
                # 开始日期与结束日期相同
                $test_date = $testdate_start;
            }
            else {
                # code...
                $test_date = "{$testdate_start}～{$testdate_end}";
            }
            if ($info_pos->test_date !== ''){
                $testDateCell = $info_pos->test_date;
                $worksheet->getCell($testDateCell)->setValue($test_date); //检测日期
            }
            //----------------------------------检测日期 end---------------------------------
            $sample_count = DB::table('a_contract_gaiyao')->where('contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->sum('count');
            if ($info_pos->sample_count !== '') {
                $sampleCountCell = $info_pos->sample_count;
                $worksheet->getCell($sampleCountCell)->setValue($sample_count . $v->unit); //样品数量
            }
            $temp_hum_pressure_data = DB::table('a_item_data as B')->select('temp', 'hum', 'pressure')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('item_id', '!=', 61)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->where('res', '<', 2)->get();
            $se = self::tempHumPressureSE($temp_hum_pressure_data);
            [$temp, $hum, $pressure] = $se;
            //------------------------------------------ 温度 start---------
            if ($info_pos->temp !== '') {
                $worksheet->getCell($info_pos->temp)->setValue($temp); //温度
            }
            //------------------------------------------ 温度 end ---------
            //------------------------------------------ 相对湿度 start---------
            if ($info_pos->hum !== '') {
                $worksheet->getCell($info_pos->hum)->setValue($hum); //相对湿度
            }
            //------------------------------------------ 相对湿度 end ---------
            //------------------------------------------ 大气压力 start---------
            if ($info_pos->pressure !== '') {
                $worksheet->getCell($info_pos->pressure)->setValue($pressure); //大气压力
            }
            //------------------------------------------ 大气压力 end ---------
            //------------------------------------------ 天气 start---------
            $wea = DB::table('a_item_data as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->where('wea', '!=', '')->value('wea');
            if ($info_pos->wea !== '') {
                if ($wea) {
                    $wea = is_numeric($wea) ? $WeaStat[$wea] : $wea;
                    $worksheet->getCell($info_pos->wea)->setValue($wea);
                } //天气
            }
            //------------------------------------------ 天气 end ---------

            $data_sample_info = DB::table('a_sample_info as B')->select('B.*', 'A.tool_name', 'A.voltage', 'A.class', 'A.std_id', 'A.tool_id', 'A.item_ids', 'A.vol_id', 'class_id')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->get();
            $rowIndex = $initRowIndex = $info_pos->line_num;
            $sampleCount = count($data_sample_info);
            $hegeCount = $unhegeCount = $weiZhiCount = 0;
            $sampleMergeCells = $this->sampleMergeCells($worksheet,$info_pos); //样品内容合并单元格
            $sample_rowspann = $info_pos->sample_rowspann;  //单个样品占用行数
            $tester_name = $audit_man = [];
            foreach ($data_sample_info as $kk => $vv) {
                $tester_name = $vv->tester_names;
                $audit_man = $vv->audit_man;
                if ($kk > 0) {
                    # 键 >0 代表 不是第一次循环
                    //在第x行之前插入n个新行，插入单元格可能会引起样式错乱(待解决)
                    if ($sample_rowspann > 7 && ($kk - 1)%3 == 0) {  //这里只考虑了斗臂车的情况，再考虑其他情况的话可以加字段
                        $worksheet->setBreak('A' . ($rowIndex-1), 1);
                    }
                    $worksheet->insertNewRowBefore((int)$rowIndex, (int)$sample_rowspann); 
                    $this->toMergeCells($kk,$info_pos,$sampleMergeCells,$worksheet);
                    $this->sampleDefVal($worksheet,$info_pos,$kk,$sampleMergeCells);
                }
                if ($info_pos->serial_num1 !== '') {
                    $worksheet->getCell($info_pos->serial_num1 . $rowIndex)->setValue($kk + 1); //序号
                }
                if ($info_pos->sample_num1 !== '') {
                    $worksheet->getCell($info_pos->sample_num1 . $rowIndex)->setValue(substr($vv->bar_code, 6) . '#'); //样品号
                }
                $self_num1 = $info_pos->self_num1;
                if ($self_num1 !== '') {
                    $worksheet->getCell($self_num1 . $rowIndex)->setValue($vv->self_num); //自编号
                    /*++++++++++++++++设置单元行高度 start++++++++++++++++*/
                    $self_num_col_min = ABC2decimal($self_num1);
                    $self_num_col_max = ABC2decimal($info_pos->self_num2);
                    $fontSize = $worksheet->getStyle($self_num1 . $rowIndex)->getFont()->getSize();
                    //字体是10号字的情况下，19个数字占17.875宽度  ，计算得出一个数字占大约0.940789宽度
                    $width = 0;
                    for($i = $self_num_col_min; $i<=$self_num_col_max; $i++){
                        $width += $worksheet->getColumnDimensionByColumn($i)->getWidth();
                    }
                    $lineCount = floor($width/0.940789); //计算每行占用的文字
                    $lines = ceil(strlen($vv->self_num)/$lineCount) ?: 1; //计算总共占用行数
                    $height = $worksheet->getRowDimension($rowIndex)->getRowHeight();
                    $height2 = $lines * 14;
                    if ($height2 > $height) {
                        $worksheet->getRowDimension($rowIndex)->setRowHeight($height2);
                    }
                    /*++++++++++++++++设置单元行高度 start++++++++++++++++*/
                }
                if ($info_pos->voltage1 !== '') {
                    $voltageWithoutKV = str_replace("kV", "", $vv->voltage); //将额定电压中的 kV去掉
                    $worksheet->getCell($info_pos->voltage1 . $rowIndex)->setValue($voltageWithoutKV); //额定电压
                }
                if ($info_pos->class1 !== '') {
                    $worksheet->getCell($info_pos->class1 . $rowIndex)->setValue($vv->class); //分类
                }
                if ($info_pos->res1 !== '') {
                    if ($vv->res === 1) {
                        $hegeCount += 1;
                        $panding = '合格';
                    }
                    elseif ($vv->res === 2) {
                        $unhegeCount += 1;
                        $panding = '不合格';
                    }
                    else {
                        $weiZhiCount += 1;
                        # code...
                        $panding = '';
                    }
                    $colRow = $info_pos->res1 . $rowIndex;
                    $worksheet->getCell($colRow)->setValue($panding); //判定
                }
                //有效日期有位置 而且 周期不为0或null
                if ($info_pos->valid_date1 != '') {
                    if ($vv->res == 1) {
                        $valid_date = $vv->next_time;
                        if(!$valid_date){
                            $itemDataCon->updateTestTime_NextTime($vv->id);
                            $valid_date = DB::table('a_sample_info')->where('id',$vv->id)->value('next_time');
                        }
                    }
                    else {
                        # 不合格
                        $valid_date = "—";
                    }
                    $worksheet->getCell($info_pos->valid_date1 . $rowIndex)->setValue($valid_date); //有效日期
                }
                //------------------------------------------ 试验条件 start ---------
                $key_condition = DB::table('a_condition_key')->select('id', 'item_id', 'pos_start', 'pos_end')->where([['std_id', '=', $vv->std_id], ['tool_id', '=', $vv->tool_id], ['pos_start', '!=', ''], ['pos_end', '!=', '']])->get();
                foreach ($key_condition as $vvv) {
                    $arr_item_id = explode(',', $vv->item_ids); //字符串 --》数组
                    $b = in_array($vvv->item_id, $arr_item_id); //in_array($value, $array);
                    if ($b) {
                        $val_condition = DB::table('a_condition')->where('condition_key_id', '=', $vvv->id)->whereIn('vol_class_id', [0, $vv->vol_id, $vv->class_id])->value('val');
                    }
                    else {
                        $val_condition = '/';
                    }
                    $vvvstart = $vvv->pos_start;
                    $vvvend = $vvv->pos_end;

                    if ($vvvstart && $vvvend) {
                        $worksheet->mergeCells($vvvstart . $rowIndex . ':' . $vvvend . $rowIndex); //试验条件  合并单元格
                        $worksheet->getCell($vvvstart . $rowIndex)->setValue($val_condition); //试验条件
                    }
                } //内内层 foreach 结尾
                
                //------------------------------------------ 试验条件 end ---------
                //------------------------------------------ 单项目的检测结果（符合、不符合） start ---------
                $item_res = DB::table('a_std_tool_item_rel')->select('id', 'testitem_id', 'pos_start', 'pos_end','line_num')->where([['std_id', '=', $vv->std_id], ['tool_id', '=', $vv->tool_id]])->get();
                // dd($item_res,$vv,$rowIndex);

                foreach ($item_res as $vvv) {
                    $arr_item_id = explode(',', $vv->item_ids); //字符串 --》数组
                    $b = in_array($vvv->testitem_id, $arr_item_id); //in_array($value, $array);
                    if ($b) {
                        # code...
                        $test_res = DB::table('a_item_data')->where([['sample_info_id', '=', $vv->id], ['item_id', '=', $vvv->testitem_id]])->value('res');
                        //结论,0-不符合，1-符合，2-未录入
                        if ($test_res === 0) {
                            $test_res = '不符合';
                        }
                        elseif ($test_res === 1) {
                            $test_res = '符合';
                        }
                        elseif ($test_res === 3) {
                            $test_res = '/';
                        }
                        else {
                            # code...
                            $test_res = '';
                        }
                    }
                    else {
                        # code...
                        $test_res = '/';
                    }
                    $vvvstart = $vvv->pos_start;
                    $vvvend = $vvv->pos_end;
                    $vvvLineNum = $vvv->line_num;
                    $rowIndexItemRes = $vvvLineNum ? $rowIndex+($vvvLineNum-$initRowIndex) : $rowIndex;
                    if ($vvvstart && $vvvend) {
                        $worksheet->getCell($vvvstart . $rowIndexItemRes)->setValue($test_res); //单项目的检测结果
                    }
                    //------------------------------------------ 试验数据 start ---------
                    $key_data = DB::table('a_testdata_key')
                            ->select('id', 'item_id', 'pos_start', 'pos_end', 'std_tool_item_rel_id','line_num')
                            ->where('std_tool_item_rel_id',$vvv->id)
                            ->where([['pos_start', '!=', ''], ['pos_end', '!=', '']])
                            ->get();
                    foreach ($key_data as $kdKey => $vvv2) {
                        $arr_item_id = explode(',', $vv->item_ids); //字符串 --》数组
                        $b = in_array($vvv2->item_id, $arr_item_id); //in_array($value, $array);

                        if ($b) {
                            $val_testdata = DB::table('a_input_data')->where([['sample_info_id', '=', $vv->id], ['testdata_key_id', '=', $vvv2->id]])->value('val');
                            // dd( "{$val_testdata} --- {$vv->id}---- {$vvv2->id}");
                        } else {
                            # code...
                            $val_testdata = '/';
                        }

                        try {
                            $vvv2start = $vvv2->pos_start;
                            $vvv2end = $vvv2->pos_end;
                            $vvv2LineNum = $vvv2->line_num;
                        }catch(\Exception $exception){
                            $vvv2start = $vvv2end = $vvv2LineNum = '';
                        }
                        if ($vvv2start && $vvv2end) {
                            $rowIndexItemData = $vvv2LineNum ? $rowIndex+($vvv2LineNum-$initRowIndex) : $rowIndex;
                            $worksheet->getCell($vvv2start . $rowIndexItemData)->setValue(' '.$val_testdata.' '); //试验数据
                        }
                    } //内内层 foreach 结尾
                    //------------------------------------------ 试验数据 end ---------

                } //内内层 foreach 结尾
                //------------------------------------------ 单项目的检测结果（符合、不符合） end   ---------
                
                $rowIndex += $sample_rowspann;
            } //内层 foreach 结尾
	        //检测结论，检验结论
            $jlcellstr = trim($worksheet->getCell('A' . $rowIndex)->getValue());
            if ($jlcellstr === '检测结论' || $jlcellstr === '检验结论') {
                $range = $worksheet->getCell('A' . $rowIndex)->getMergeRange();
                $rangeAry = explode(':', $range);
                $ABCpos = dec2ABC(ABC2decimal(substr($rangeAry[1], 0, 1)) + 1);
                $worksheet->getCell($ABCpos . $rowIndex)->setValue("总数：$sampleCount 件,其中合格：$hegeCount 件，不合格：$unhegeCount 件,未知结论：$weiZhiCount 件");
                self::setPic($worksheet,$ABCpos . $rowIndex,'./storage/ExcelTemplate/report_sign_hege.png',50);
            }
            $lastCol = $worksheet->getHighestColumn();
            $worksheet->getStyle("A{$initRowIndex}:{$lastCol}{$rowIndex}")->getAlignment()->setWrapText(true);

            /*****插入试验人员*******/
            //检测人员
            $insertRowIndex = $rowIndex+1;
            //title合并范围
            $title_range = $worksheet->getCell('A' . $insertRowIndex)->getMergeRange();
            //value合并范围
            $value_range = $worksheet->getCell($ABCpos . $insertRowIndex)->getMergeRange();
            $worksheet->insertNewRowBefore($insertRowIndex, 1);
            $worksheet->mergeCells($title_range)->setCellValue('A'.$insertRowIndex, '试验人员');
            $worksheet->mergeCells($value_range)->setCellValue($ABCpos . $insertRowIndex, $tester_name);
            /*****插入审核人员*******/
            //审核人员
            $insertRowIndex = $rowIndex+2;
            //title合并范围
            $title_range = $worksheet->getCell('A' . $insertRowIndex)->getMergeRange();
            //value合并范围
            $value_range = $worksheet->getCell($ABCpos . $insertRowIndex)->getMergeRange();
            $worksheet->insertNewRowBefore($insertRowIndex, 1);
            $worksheet->mergeCells($title_range)->setCellValue('A'.$insertRowIndex, '审核人员');
            $worksheet->mergeCells($value_range)->setCellValue($ABCpos . $insertRowIndex, $audit_man);
        } //最外层 foreach 结尾

        /*$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet2, 'Xls');
        //----------直接在浏览器下载----start
        $file_name = "检测报告" . $batchNum . ".xls"; //输出的文件名称
        header('Content-Type: application/vnd.ms-excel'); //告诉浏览器输出Excel 03版本文件
        header('Content-Disposition: attachment;filename=' . urlencode($file_name)); //告诉浏览器输出的文件名称
        header('Cache-Control: max-age=0'); //禁止缓存
        //----------直接在浏览器下载----end
        $writer->setPreCalculateFormulas(false); //禁用公式预先计算
        $writer->save('php://output'); //直接在浏览器下载
        //为了防止内存泄露，建议用完手动清理
        $spreadsheet2->disconnectWorksheets();
        unset($spreadsheet2);*/

        //将页面定位到首页
        $spreadsheet2->setActiveSheetIndex(0);
        $file_name = "{$batchNum}.xls";
        return self::saveDown($spreadsheet2,$file_name,$batchNum);
    }

    public static function saveDown($spreadsheet,$file_name,$batchNum){
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        $writer->setPreCalculateFormulas(false); //禁用公式预先计算
        $storeFile = request('storeFile','reports/'.$batchNum.'/'.$file_name);
        $disk = Storage::disk('public');
        $disk->put($storeFile,''); //创建路径
        $path = $disk->path($storeFile);
        $writer->save($path);
        //为了防止内存泄露，建议用完手动清理
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);

        if (request('toPdf',0) == 1) {
            //因为目前在用的转pdf插件无法满足中文文件名，所以这里修改文件名为随机数，最后删除所有文件
            $pathinfo = pathinfo($path);
            $ext = '.' . $pathinfo['extension'];
            $self_num = $batchNum . '';
            $path2 = $pathinfo['dirname'] . '/' . $self_num . $ext;
            copy($path, $path2);
            $path = substr($path2, 0, -strlen($ext)) . '.pdf';
            @unlink($path);
            if (config('constant.is_lan')) {
                $url = 'http://192.168.8.150:999';
            } else {
                $url = 'http://exceltopdf.hbjc2018.cn';
            }
            $url = $url . '?filepath=' . urlencode($path2);
            curl($url);
            $storeFile = substr($storeFile, 0, -strlen('.xls')) . '.pdf';
        }
        if (request('download',false)) {
            if (config('constant.app_name') == 'HanZhongHanYuan') {
                $path2 = dirname($path).'/'.substr($file_name,0,-strlen('.xls')).'.pdf';
                copy($path,$path2);
                return response()->download($path2);
            }
            return response()->download($path);
        }
        return $path;
    }



    //温湿度，大气压最大最小值
    public static function tempHumPressureSE($data,$unit=true) {
        if (is_object($data)) {
            $data = $data->toArray();
        }
        $columns = ['temp'=>1,'hum'=>1,'pressure'=>2];  //字段=》保留小数点位数
        $temp = $hum = $pressure = ''; //初始化
        foreach ($columns as $column => $float) {
            $$column = ary_filter(array_column($data,$column));
            $min = $$column ? min($$column) : '';
            $max = $$column ? max($$column) : '';
            $min = $min ? sprintf("%1\$.".$float."f",$min) : '';
            $max = $max ? sprintf("%1\$.".$float."f",$max) : '';
            unset($$column);
            $$column =  ($min !== '' && $max !== '' && $min != $max) ? "({$min}～{$max})" : ($max ? $max : '');
        }
        if ($unit) {
            return [$temp.'℃', $hum.'%RH', $pressure.'kPa'];
        }
        return [$temp, $hum, $pressure];
    }

    //获取第一个样品的表格信息（合并信息及数据）
    public function sampleMergeCells($worksheet,$info_pos){
        $mergeCells = $worksheet->getMergeCells();
        $initRowIndex = $info_pos->line_num;
        $intRowIndexMax = $initRowIndex + $info_pos->sample_rowspann - 1;
        $toMerge = [];
        foreach($mergeCells as $cells){
            $cell = explode(':',$cells)[0];
            $len = strlen($cell);
            for($i = 0; $i < $len; $i++) {
                if(!preg_match('/[A-Za-z]/',substr($cell,$i,1))){
                    $int = substr($cell,$i);
                    if($int >= $initRowIndex && $int <= $intRowIndexMax){
                        $toMerge[] = $cells;
                    }
                    continue;
                }
            }
        }
        return $toMerge;
    }

    /*合并新增加的相关行，并对有数据的行添加内容*/
    public function toMergeCells($kk,$info_pos,$sampleMergeCells,$worksheet){
        $sample_rowspann = $info_pos->sample_rowspann;
        foreach($sampleMergeCells as $cells){
            $cellsAry = explode(':',$cells);
            $startABCint = $this->splitABCint($cellsAry[0]);
            $endABCint = $this->splitABCint($cellsAry[1]);
            $toMergeCells[] = [$startABCint,$endABCint];
            $start = $startABCint[0] . ($startABCint[1] + $kk*$sample_rowspann);
            $end = $endABCint[0] . ($endABCint[1] + $kk*$sample_rowspann);
            $toMerge = "{$start}:{$end}";
            //合并
            $worksheet->mergeCells($toMerge);
        }
    }

    private function splitABCint($str){
        $len = strlen($str);
        for($j = 0; $j < $len; $j++) {
            if(!preg_match('/[A-Za-z]/',substr($str,$j,1))){
                $int = substr($str,$j-1);
                $abc = substr($str,0,$j-1);
            }
        }
        return [$abc,$int];
    }

    public function sampleDefVal($worksheet,$info_pos,$kk,$sampleMergeCells){
        $sample_rowspann = $info_pos->sample_rowspann;
        $initRowIndex = (int)$info_pos->line_num;
        $Amerge = $worksheet->getCell('A1')->getMergeRange();
        if (!$Amerge) {
            die($info_pos->tool_name.'模板文件错误！');
        }
        $maxMerge = explode(':',$Amerge)[1];
        // dd($maxMerge);
        $maxColumn = $worksheet->getCell($maxMerge)->getColumn();
        $maxRowIndex = $initRowIndex + $sample_rowspann - 1;
        $mergeCells = $sampleMergeCells;

        for($i = 1; $i <= ABC2decimal($maxColumn); $i ++) {
            for($j = $initRowIndex; $j <= $maxRowIndex; $j ++) {
                $col = dec2ABC($i);
                $cell = $col.$j;
                $range = $worksheet->getCell($cell)->getMergeRange();
                if(in_array($range,$mergeCells)){
                    continue;
                }
                if($range){
                    $mergeCells[] = $range;
                }
            }
        }
        foreach($mergeCells as $cells){
            $cell = explode(':',$cells)[0];
            if($def = $worksheet->getCell($cell)->getValue()) {
                $col = $worksheet->getCell($cell)->getColumn();
                $row = substr($cell,strlen($col));
                $row2 = $row + $kk*$sample_rowspann;
                $cell = $col.$row2;
                $worksheet->getCell($cell)->setValue($def);
            }
        }
    }


    public static function setPage($worksheet,$coor = 'A1-JZ'){
        $worksheet->getPageSetup()->setHorizontalCentered(true);
        $footerPageMargins = 0.3; //页脚的页边距
        $bottomPageMargins = 0.4; //下边的页边距
        $headerPageMargins = 0.4; //眉头的页边距
        $worksheet->getPageMargins()->setRight(0)->setLeft(0)->setHeader($headerPageMargins)->setFooter($footerPageMargins)->setBottom($bottomPageMargins); //设置页边距
        if ($coor) {
            self::setPic($worksheet,$coor,'./storage/ExcelTemplate/report_sign.png',200);
        }
    }
    /*
    excel中填充图片
    $coor 如果它是由单元格-JZ组成，则是居中
    */
    public static function setPic($worksheet,$coor,$pic,$height=60){
        if (file_exists($pic)) {
            // dd(111);
            $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $drawing->setPath($pic);//图片的路径
            $drawing->setHeight($height);    //高度
            $drawing->setResizeProportional(true);  //true 是保持宽高比
            $list = explode('-', $coor);
            $coor = $list[0];
            if (array_get($list,1,'') == 'JZ') {  //设置图片居中
                $mergecells = $worksheet->getCell($coor)->getMergeRange();
                if($mergecells){
                    preg_match('/[0-9]+/',$mergecells,$rowMatch);
                    preg_match_all('/[A-Za-z]+/',$mergecells,$matches);
                    list($start,$end) = $matches[0];
                    $start = ABC2decimal($start);
                    $end = ABC2decimal($end);
                    $width = $width2 = 0;
                    for ($i=$start; $i <= $end ; $i++) { 
                        $width += $worksheet->getColumnDimension(dec2ABC($i))->getWidth();
                    }
                    $offsetx2 = ($width-$height/10)/2;
                    for ($i=$start; $i <= $end ; $i++) { 
                        $col = dec2ABC($i);
                        $width2 += $worksheet->getColumnDimension($col)->getWidth();
                        if($width2 > $offsetx2){
                            $coor = $col.$rowMatch[0];
                            break;
                        }
                    }
                }   
            }
            $drawing->setCoordinates($coor);//指定位置
            $drawing->setOffsetX(1);
            $drawing->setWorksheet($worksheet);
        }
    }

}
